package com.dongge.poi.excel;

import com.dongge.poi.entity.Student;
import com.dongge.poi.hold.StyleHold;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;

@RestController
@RequestMapping("/excel")
public class ExcelController {

    @GetMapping("/create")
    public byte[] createExcel() throws NoSuchFieldException, IllegalAccessException, IOException {


        Workbook wb = new XSSFWorkbook();

        Sheet sheet = wb.createSheet("new sheet");
        StyleHold styleHold = new StyleHold(wb);


        List<Student> students = getStudents();

        String[] arr = {"name","age","date","money"};
        for (int i = 0; i < students.size(); i++) {
            Row row = sheet.createRow(i);
            Student student = students.get(i);
            for (int x = 0; x < arr.length; x++) {
                Cell cell = row.createCell(x);
                setValue(cell,student,arr[x],styleHold);
            }

        }

        String tempStr = null;
        int startIndex=0;
        int endIndex=0;
        for (int i = 0; i < sheet.getLastRowNum()+1; i++) {
            Cell cell = sheet.getRow(i).getCell(0);
            String stringCellValue = cell.getStringCellValue();

            if (i > 0) {
                if (tempStr .equals(stringCellValue) ) {
                    endIndex++;
                    //最后一个
                    if (i == sheet.getLastRowNum()) {
                        if (endIndex - startIndex > 0) {

                            sheet.addMergedRegion(new CellRangeAddress(
                                    startIndex, //first row (0-based)
                                    endIndex, //last row  (0-based)
                                    0, //first column (0-based)
                                    0  //last column  (0-based)
                            ));
                        }
                    }
                } else {
                    if (endIndex - startIndex > 0) {

                        sheet.addMergedRegion(new CellRangeAddress(
                                startIndex, //first row (0-based)
                                endIndex, //last row  (0-based)
                                0, //first column (0-based)
                                0  //last column  (0-based)
                        ));
                    }
                    startIndex = ++endIndex;
                }
            }
            tempStr = stringCellValue;
        }





      /*  try (OutputStream fileOut = new FileOutputStream("D:\\workbook.xlsx")) {
            wb.write(fileOut);
        }catch (Exception e){

        }*/

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        wb.write(out);
        return out.toByteArray();
    }

    private void setValue(Cell cell,Student student,String name,StyleHold styleHold) throws NoSuchFieldException, IllegalAccessException {
        Class<? extends Student> aClass = student.getClass();
        Field field = aClass.getDeclaredField(name);
        field.setAccessible(true);
        Class<?> type = field.getType();
        if(type .equals(String.class)){
            cell.setCellValue((String)field.get(student));
            cell.setCellStyle(styleHold.getDefaultStyle());
        }else if(type .equals(Integer.class)){
            cell.setCellValue((Integer)field.get(student));
            cell.setCellStyle(styleHold.getDefaultStyle());
        }else if(type.equals(Date.class)){
            cell.setCellValue((Date)field.get(student));
            cell.setCellStyle(styleHold.getDateStyle());
        }else if(type.equals(Double.class)){
            cell.setCellValue((Double)field.get(student));
            cell.setCellStyle(styleHold.getDataStyle());
        }
    }

    private List<Student> getStudents(){
        List<Student> list = new ArrayList<>();
        Student s1 = new Student();
        s1.setName("小王");
        s1.setAge(25);
        s1.setDate(new Date());
        s1.setMoney(2000.001);

        Student s3 = new Student();
        s3.setName("小王");
        s3.setAge(22);
        s3.setDate(new Date());
        s3.setMoney(1000.001);

        Student s2 = new Student();
        s2.setName("小李");
        s2.setAge(20);
        s2.setDate(new Date());
        s2.setMoney(2400000.001);
        list.add(s1);
        list.add(s3);
        list.add(s2);
        return list;
    }
}
